package portfelo.db;

import java.sql.*;
import java.sql.Date;
import java.util.*;

import portfelo.dto.AccountDto;
import portfelo.dto.PhotoDto;
import portfelo.dto.TagDto;


//Assuming that every image has at minimum one tag
//And every artist has at minimum one image
//else will have to add in checks for if they have none

//only two major events happen in controller - querydb interaction
//FIRST: controller changes the tag: 
//	1. Change tag: setNextTag(), setPreviousTag(), setTag(TagDto a)
//	3. Return the new tag
//controller calls one method to do this set.NextTag(), setPreviousTag(), setTag(TagDto a)
//second is getCurrentTag() to get the new tag

//SECOND: controller asks for next/previous image: getNextImage()
//two different circumstanses: 1) next image but same tag or 2)tags are changed and new image
//	1. If the tags were changed, then gets images for new image
//	1. Change current image to the next in the list: getNextImage(), getPreviousImage()
//	2. Change list of tags for the current image: setTagsForCurrentImage()
//	3. Return the current image
// 	4. Return the tags for the current image: getTags()
//controller calls two methods for this, the first is getNextImage() to get the image
//the second is getTags() to get the list of tags for it

//getNextImageForArtist(Artist a) or getNextImageForArtist(String a) would be used for if we implement a search option
//to let them select a particular artist
public class QueryDB
{
	private Connection conn;
	private Statement state;
	private List<TagDto> tags; //list of tags for the current image
	private PhotoDto currentImage; //currently selected image
	private TagDto currentTag; //currently selected tag
	private List<PhotoDto> tagImages; //List of images with the current tag
	
	private TagDto pastTag; //used to check if tags were changed when changing images
	int imageIndex;
	int tagIndex;

	public QueryDB()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql:///portfelo","root", "furikuri");
			state = conn.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		tags = new ArrayList<TagDto>();
		
		try {
			ResultSet res = state.executeQuery("select * from photo limit 1");
			res.next();
			//initialized to a image in db, i think first
			//public Image(int id, String url, String fqn, int accId, String loc, String title, Date date)
			currentImage = new PhotoDto();
			currentImage.setAccountId(res.getLong("Account_ID"));
			currentImage.setDateTaken(res.getDate("date_taken"));
			currentImage.setFullyQualifiedName(res.getString("fully_qualified_name"));
			currentImage.setLocation(res.getString("location"));
			currentImage.setPhotoId(res.getLong("photo_id"));
			currentImage.setTitle(res.getString("title"));
			currentImage.setPhotoTagList(new ArrayList<TagDto>());
			
			List<TagDto> temp = getTagsForImage(currentImage.getPhotoId());
			currentImage.setPhotoTagList(temp);
			res.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			//an exception is if there exists no images/ tags in db
		}
		
		
		setTagsForCurrentImage();
		getImages();
	}
	


	public PhotoDto getPreviousImage()
	{
		if(!pastTag.equals(currentTag))
			getImages();
		
		if(imageIndex == 0)
			imageIndex = tagImages.size() - 1;
		
		currentImage = tagImages.get(imageIndex--);
		setTagsForCurrentImage();
		
		return currentImage;
	}
	
	//returns the next image in the currently selected tag
	public PhotoDto getNextImage()
	{
		if(!pastTag.equals(currentTag))
			getImages();
		
		if(imageIndex >= tagImages.size())
			imageIndex = 0;
			
		currentImage = tagImages.get(imageIndex++);
		setTagsForCurrentImage();
		
		return currentImage;
	}
	
	public PhotoDto getCurrentImage()
	{
		return currentImage;
	}
	
	public List<PhotoDto> getCurrentTagImages()
	{
		return tagImages;
	}
	
	//TODO FIX
	//returns the next image, changing the currently selected tag to 'a'
//	public PhotoDto getNextImageForArtist(TagDto a)
//	{
//		//if the currenttag has not changed then just return the next image in the list
//		if(currentTag == a)
//		{
//			return getNextImage();
//		}
//		
//		currentTag = a;
//		getImages();
//		
//		imageIndex = 0;
//		currentImage = tagImages.get(imageIndex++);
//		setTagsForCurrentImage();
//		
//		return currentImage;
//	}
	
	//TODO FIX
	//same as above but receives an account name (or maybe account_id)
//	public PhotoDto getNextImageForArtist(String a)
//	{
//		Artist temp = null;
//		try {
//			ResultSet res = state.executeQuery("SELECT * FROM account WHERE account_name = '" + a + "'");
//			res.next();
//			//Artist(int id, String name, int status, String first, String last, String loc, String avatar, 
//			//String descrip, Date d, int hits)
//			temp = new Artist(res.getInt("account_id"), res.getString("account_name"), res.getInt("status"), 
//					res.getString("first_name"), res.getString("last_name"), res.getString("location"), 
//					res.getString("avatar"), res.getString("description"), res.getDate("member_since"), res.getInt("hits"));
//			res.close();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		
//		//if the currenttag has not changed then just return the next image in the list
//		if(currentTag == temp)
//		{
//			return getNextImage();
//		}
//		
//		getImages();
//		setTagsForCurrentImage();
//		
//		return getNextImage();
//	}
	
	
	//returns the next artist/tag
//	public Artist getNextArtist()
//	{
//		return null;
//	}
	//commented out, just do setNextTag(), then getCurrentTag()
	
	
	//gets the images for the current tag, used when changing image after tags are changed
	private void getImages()
	{
		tagImages = new ArrayList<PhotoDto>();
		try {
			ResultSet res = state.executeQuery("select * from photo p, tag t where p.photo_id = t.photo_id and " +
					"t.account_id = '" + currentTag.getAccountDto().getAccountId() + "' AND t.role = '" +
					currentTag.getRole() + "'");
			
			while(res.next())
			{
				PhotoDto d = new PhotoDto();
				d.setAccountId(res.getLong("Account_ID"));
				d.setDateTaken(res.getDate("date_taken"));
				d.setFullyQualifiedName(res.getString("fully_qualified_name"));
				d.setLocation(res.getString("location"));
				d.setPhotoId(res.getLong("photo_id"));
				d.setTitle(res.getString("title"));
				d.setPhotoTagList(new ArrayList<TagDto>());
				
				List<TagDto> temp = getTagsForImage(currentImage.getPhotoId());
				d.setPhotoTagList(temp);
				tagImages.add(d);
			}
			res.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		imageIndex = 0;
	}
	
	//up arrow, change to next tag
	public void setNextTag()
	{
		if(tagIndex >= tags.size())
			tagIndex = 0;
		
		currentTag = tags.get(tagIndex++);
	}
	
	//down arrow, change current tag to previous tag
	public void setPreviousTag()
	{
		if(tagIndex <= 0)
			tagIndex = tags.size() - 1;
		
		currentTag = tags.get(tagIndex--);
	}
	
	//when user clicks on a tag name, changes current tag to the selected tag
	public void setTag(TagDto a)
	{
		currentTag = a;
	}
	
	//TODO
	public void setTag(String a)
	{
//		currentTag = a;
	}
	
	//returns list of tags
	public List<TagDto> getTags()
	{
		return tags;
	}
	
	//returns the current tag
	public TagDto getCurrentTag()
	{
		return currentTag;
	}
	
	//change the current list of tags for the current image
	//current tag remains the same
	private void setTagsForCurrentImage()
	{
		tags = currentImage.getPhotoTagList();
	}
	
	private List<TagDto> getTagsForImage(long photoId) 
	{
		List<TagDto> ret = new ArrayList<TagDto>();
		try {
			Statement getTagsState = conn.createStatement();
			ResultSet results = getTagsState.executeQuery("select * from tag where photo_id = " + photoId);
			while(results.next())
			{
				TagDto k = new TagDto();
				k.setAccountDto((getAccount(results.getLong("account_id"))));
				k.setPhotoId(results.getLong("photo_id"));
				k.setRole(results.getString("role"));
				k.setTagId(results.getLong("tag_id"));
				ret.add(k);
			}
			results.close();
			getTagsState.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return ret;
	}
	
	private AccountDto getAccount(long accountId)
	{
		AccountDto ret = new AccountDto();
		try {
			Statement getAccountState = conn.createStatement();
			ResultSet results = getAccountState.executeQuery("select * from account where account_id = " + accountId);
			results.next();
			ret.setAccountId(accountId);
			ret.setAccountName(results.getString("account_name"));
			ret.setAccountPassword(results.getString("account_password"));
			ret.setAvatar(results.getString("avatar"));
			ret.setFirstName(results.getString("first_name"));
			ret.setLastName(results.getString("last_name"));
			
			results.close();
			getAccountState.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return ret;
	}

	//maybe not necessary
	public void close()
	{
		try {
			state.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}
